const mysql = require('mysql2/promise');

// 数据库配置
const dbConfig = {
  host: '43.143.253.188',
  user: 'nextjs',
  password: 'yEAiRE2jcxTyRBy5',
  database: 'nextjs',
  charset: 'utf8mb4'
};

// 标准数据格式（从analyze-standard-data.js导入）
const standardData = {
  // 1418指令：选择区域
  "1418": {
    "areaId:100": [
      { id: 289, name: "许昌" },
      { id: 302, name: "洛阳" },
      { id: 304, name: "长安" }
    ],
    "areaId:19": [
      { id: 70, name: "成都" },
      { id: 111, name: "南中" },
      { id: 117, name: "汉中" }
    ],
    "areaId:43": [
      { id: 154, name: "建业" },
      { id: 212, name: "长沙" },
      { id: 264, name: "江陵" }
    ]
  },

  // 1436指令：获取站点信息
  "1436": {
    "pid:289": [ // 许昌子区域
      { id: 2, name: "新手村", extension: "(1级)" },
      { id: 301, name: "陈留", extension: "(5级)" },
      { id: 487, name: "九里山", extension: "(20级)" },
      { id: 533, name: "芒砀山", extension: "(30级)" }
    ],
    "pid:302": [ // 洛阳子区域
      { id: 285, name: "官渡", extension: "(40级)" },
      { id: 286, name: "虎牢关", extension: "(50级)" },
      { id: 333, name: "乌巢", extension: "(80级)" },
      { id: 337, name: "函谷关", extension: "(55级)" }
    ],
    "pid:304": [ // 长安子区域
      { id: 303, name: "华山", extension: "(90级)" },
      { id: 311, name: "潼关", extension: "(75级)" },
      { id: 414, name: "五丈原", extension: "(65级)" }
    ],
    "pid:70": [ // 成都子区域
      { id: 93, name: "落凤坡", extension: "(30级)" },
      { id: 112, name: "广汉", extension: "(40级)" },
      { id: 113, name: "峨眉山", extension: "(50级)" }
    ],
    "pid:111": [ // 南中子区域
      { id: 52, name: "云南", extension: "(60级)" },
      { id: 143, name: "泸水", extension: "(70级)" },
      { id: 351, name: "西洱湖", extension: "(80级)" }
    ],
    "pid:117": [ // 汉中子区域
      { id: 306, name: "子午谷", extension: "(90级)" },
      { id: 307, name: "街亭", extension: "(100级)" },
      { id: 308, name: "上方谷", extension: "(120级)" }
    ],
    "pid:154": [ // 建业子区域
      { id: 267, name: "广陵", extension: "" }
    ],
    "pid:212": [ // 长沙子区域
      { id: 220, name: "巴陵", extension: "" },
      { id: 221, name: "洞庭湖", extension: "" },
      { id: 222, name: "湘水", extension: "(130级)" }
    ],
    "pid:264": [ // 江陵子区域
      { id: 137, name: "麦城", extension: "(140级)" },
      { id: 161, name: "长坂坡", extension: "(140级)" },
      { id: 163, name: "夷陵", extension: "" }
    ]
  }
};

// 提取所有需要的ID和城市名
const requiredIds = new Set();
const cityNameToId = new Map();
const idToCityName = new Map();

// 从1418指令中提取
Object.values(standardData["1418"]).forEach(areas => {
  areas.forEach(area => {
    requiredIds.add(area.id);
    cityNameToId.set(area.name, area.id);
    idToCityName.set(area.id, area.name);
  });
});

// 从1436指令中提取
Object.values(standardData["1436"]).forEach(areas => {
  areas.forEach(area => {
    requiredIds.add(area.id);
    cityNameToId.set(area.name, area.id);
    idToCityName.set(area.id, area.name);
  });
});

async function syncMapAreasWithStandard () {
  const connection = await mysql.createConnection(dbConfig);

  try {
    console.log('🔧 开始同步地图区域数据与标准格式...\n');

    // 1. 检查当前数据库状态
    console.log('📋 步骤1: 检查当前数据库状态...');
    const [totalCount] = await connection.execute('SELECT COUNT(*) as total FROM map_areas');
    console.log(`  总区域数: ${totalCount[0].total}`);

    // 2. 检查哪些标准ID已被占用
    console.log('\n📋 步骤2: 检查标准ID占用情况...');
    const occupiedIds = [];
    const availableIds = [];

    for (const id of requiredIds) {
      const [rows] = await connection.execute(
        'SELECT id, name, parent_id, extension FROM map_areas WHERE id = ?',
        [id]
      );

      if (rows.length > 0) {
        const area = rows[0];
        occupiedIds.push({ id, area });
        console.log(`  ❌ ID ${id} 被占用: ${area.name} (父级: ${area.parent_id || 'NULL'})`);
      } else {
        availableIds.push(id);
        console.log(`  ✅ ID ${id} 可用`);
      }
    }

    console.log(`\n📊 ID占用统计:`);
    console.log(`  被占用的ID: ${occupiedIds.length} 个`);
    console.log(`  可用的ID: ${availableIds.length} 个`);

    // 3. 为被占用的ID重新分配新ID
    if (occupiedIds.length > 0) {
      console.log('\n📋 步骤3: 重新分配被占用的ID...');

      // 获取当前最大ID
      const [maxIdResult] = await connection.execute('SELECT MAX(id) as maxId FROM map_areas');
      let nextAvailableId = (maxIdResult[0].maxId || 0) + 1;

      const idMappings = new Map(); // 旧ID -> 新ID的映射

      for (const { id: oldId, area } of occupiedIds) {
        // 检查新ID是否可用
        while (requiredIds.has(nextAvailableId)) {
          nextAvailableId++;
        }

        // 更新区域ID
        await connection.execute(
          'UPDATE map_areas SET id = ? WHERE id = ?',
          [nextAvailableId, oldId]
        );

        idMappings.set(oldId, nextAvailableId);
        console.log(`  🔄 ID ${oldId} (${area.name}) -> 新ID ${nextAvailableId}`);

        nextAvailableId++;
      }

      console.log(`\n📊 ID重新分配完成，共处理 ${occupiedIds.length} 个区域`);
    }

    // 4. 创建或更新标准区域
    console.log('\n📋 步骤4: 创建/更新标准区域...');

    let createdCount = 0;
    let updatedCount = 0;

    // 处理1418指令中的区域（顶级区域）
    for (const [key, areas] of Object.entries(standardData["1418"])) {
      for (const area of areas) {
        const [existing] = await connection.execute(
          'SELECT id, name, parent_id FROM map_areas WHERE id = ?',
          [area.id]
        );

        if (existing.length > 0) {
          // 更新现有区域
          await connection.execute(
            'UPDATE map_areas SET name = ?, parent_id = NULL, updated_at = NOW() WHERE id = ?',
            [area.name, area.id]
          );
          console.log(`  🔄 更新区域: ${area.name} (ID: ${area.id})`);
          updatedCount++;
        } else {
          // 创建新区域
          await connection.execute(
            'INSERT INTO map_areas (id, name, parent_id, extension, created_at, updated_at) VALUES (?, ?, NULL, NULL, NOW(), NOW())',
            [area.id, area.name]
          );
          console.log(`  ✅ 创建区域: ${area.name} (ID: ${area.id})`);
          createdCount++;
        }
      }
    }

    // 处理1436指令中的子区域
    for (const [key, areas] of Object.entries(standardData["1436"])) {
      const parentId = parseInt(key.replace('pid:', ''));

      for (const area of areas) {
        const [existing] = await connection.execute(
          'SELECT id, name, parent_id, extension FROM map_areas WHERE id = ?',
          [area.id]
        );

        if (existing.length > 0) {
          // 更新现有区域
          await connection.execute(
            'UPDATE map_areas SET name = ?, parent_id = ?, extension = ?, updated_at = NOW() WHERE id = ?',
            [area.name, parentId, area.extension, area.id]
          );
          console.log(`  🔄 更新子区域: ${area.name}${area.extension} (ID: ${area.id}, 父级: ${parentId})`);
          updatedCount++;
        } else {
          // 创建新区域
          await connection.execute(
            'INSERT INTO map_areas (id, name, parent_id, extension, created_at, updated_at) VALUES (?, ?, ?, ?, NOW(), NOW())',
            [area.id, area.name, parentId, area.extension]
          );
          console.log(`  ✅ 创建子区域: ${area.name}${area.extension} (ID: ${area.id}, 父级: ${parentId})`);
          createdCount++;
        }
      }
    }

    // 5. 验证结果
    console.log('\n📋 步骤5: 验证同步结果...');

    const [finalTotalCount] = await connection.execute('SELECT COUNT(*) as total FROM map_areas');
    const [finalParentCount] = await connection.execute('SELECT COUNT(*) as total FROM map_areas WHERE parent_id IS NOT NULL');
    const [finalExtensionCount] = await connection.execute('SELECT COUNT(*) as total FROM map_areas WHERE extension IS NOT NULL AND extension != ""');

    console.log(`  总区域数: ${finalTotalCount[0].total}`);
    console.log(`  有父级区域数: ${finalParentCount[0].total}`);
    console.log(`  有等级信息区域数: ${finalExtensionCount[0].total}`);

    // 显示一些示例
    console.log('\n📋 同步后的区域示例:');
    const [sampleAreas] = await connection.execute(
      'SELECT id, name, parent_id, extension FROM map_areas WHERE id IN (?) ORDER BY id LIMIT 10',
      [Array.from(requiredIds).slice(0, 10)]
    );

    sampleAreas.forEach(area => {
      const parentInfo = area.parent_id ? `(父级: ${area.parent_id})` : '(顶级)';
      const extensionInfo = area.extension ? ` ${area.extension}` : '';
      console.log(`  ID: ${area.id.toString().padStart(4)} | ${area.name}${extensionInfo} | ${parentInfo}`);
    });

    console.log('\n🎉 地图区域数据同步完成！');
    console.log(`📊 统计: 创建 ${createdCount} 个, 更新 ${updatedCount} 个`);

  } catch (error) {
    console.error('❌ 同步失败:', error);
  } finally {
    await connection.end();
  }
}

// 运行脚本
if (require.main === module) {
  syncMapAreasWithStandard().catch(console.error);
}

module.exports = { syncMapAreasWithStandard }; 